package tt.dz.dao.ext.common;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.stereotype.Repository;

import tt.dz.dao.base.OmsCommonDao;
import tt.dz.entity.base.OmsCommonParam;
import tt.dz.entity.exception.OmsDaoException;
import tt.dz.entity.station.OmsStation;
import tt.dz.entity.system.OmsOperStation;

/**
  *用户站点查询 和参数列表
 * @author huangaho
 * @date 2015年1月24日
 */
@Repository
public class OmsStationOperDao extends OmsCommonDao<OmsStation> 
{
	
	private static final String INSERT_SQL = "insert into oms_oper_station(oper_id,station_id,valid_flag,create_time,create_oper_id,update_time,update_oper_id) values (?,?,1,now(),?,now(),?)";
	private static final String DELETE_SQL = "delete from oms_oper_station where oper_id = ? and  station_id = ?";
	/**
	 * 查询运营商的所有站点信息 条件过滤
	 */
	private static final String FIND_OPERMERCHANT_STATION = "select * from oms_station where oper_merchant_id = (select entity_id from oms_oper_list where oper_id = ? and entity_type = 1 and valid_flag = 1) and valid_flag = 1 ";
	private static final String FIND_COUNT_OPERMERCHANT_STATION = "select count(*) from oms_station where oper_merchant_id = (select entity_id from oms_oper_list where oper_id = ? and entity_type = 1 and valid_flag = 1) and valid_flag = 1 ";
	
	private static final String FIND_OPMB_STATIONS = "select * from oms_station os where  exists (select 1 from oms_oper_station oos where oos.station_id = os.station_id and oper_id = ? and oper_type = ? and  valid_flag = 1 ) and valid_flag = 1 ";
	private static final String FIND_COUNT_OPMB_STATIONS = "select count(*) from oms_station os where  exists (select 1 from oms_oper_station oos where oos.station_id = os.station_id and oper_id = ? and oper_type = ? and  valid_flag = 1 ) and valid_flag = 1 ";
	/**
	 * 查询运营商的所有站点信息 条件过滤
	 * @param param
	 * @param os
	 * @param operId
	 * @return
	 * @throws OmsDaoException 
	 */
	public List<OmsStation> findPageOrAllByOperMerchant(OmsCommonParam  param,
			OmsStation os,String operId) throws OmsDaoException
	{
		try
		{
			List<Object> queryParamList = new ArrayList<>();
			queryParamList.add(operId);
			return this.findExampleBySql(os, param.getPageNo(), param.getPageSize(), 
					param.getSortName(), param.getSortValue(), FIND_OPERMERCHANT_STATION, 
					queryParamList );
		}
		catch(Exception e)
		{
			throw new OmsDaoException(e);
		}
	}
	
	public int findCountPageOrAllByOperMerchant(OmsCommonParam  param,
			OmsStation os,String operId) throws OmsDaoException
	{
		try
		{
			List<Object> queryParamList = new ArrayList<>();
			queryParamList.add(operId);
			return this.queryTotalBySql(os, FIND_COUNT_OPERMERCHANT_STATION, queryParamList);
		}
		catch(Exception e)
		{
			throw new OmsDaoException(e);
		}
	}
	/**
	 * 查询运营商分分支机构的关联到的站点
	 * @param param
	 * @param os
	 *  @param operId
	 * @return
	 * @throws OmsDaoException 
	 */
	public List<OmsStation> findPageOrAllByEntityType(OmsCommonParam  param, OmsStation os,
			String operId,int entityType) throws OmsDaoException
	{
		try
		{
			List<Object> queryParamList = new ArrayList<>();
			queryParamList.add(operId);
			queryParamList.add(entityType);
			return this.findExampleBySql(os, param.getPageNo(), param.getPageSize(), 
					param.getSortName(), param.getSortValue(), FIND_OPMB_STATIONS, 
					queryParamList );
		}
		catch(Exception e)
		{
			throw new OmsDaoException(e);
		}
		
	}
	
	public int findCountByEntityType(OmsCommonParam  param, OmsStation os,String operId,int entityType) throws OmsDaoException
	{
		try
		{
			List<Object> queryParamList = new ArrayList<>();
			queryParamList.add(operId);
			queryParamList.add(entityType);
			return this.queryTotalBySql(os, FIND_COUNT_OPMB_STATIONS, queryParamList);
		}
		catch(Exception e)
		{
			throw new OmsDaoException(e);
		}
		
	}
	/**
	 * 
	 * @param omsOperStation
	 * @throws OmsDaoException
	 */
	public void batchAddStationOperMerchant(final List<OmsOperStation> omsOperStation) throws OmsDaoException
	{
		String sql = INSERT_SQL;
		try
		{
			this.getJdbcTemplate().batchUpdate(sql, new BatchPreparedStatementSetter() {
				
				@Override
				public void setValues(PreparedStatement arg0, int arg1) throws SQLException 
				{
					int index = 1;
					OmsOperStation oos = omsOperStation.get(arg1);
					arg0.setString(index++, oos.getOper_id());
					arg0.setString(index++, oos.getStation_id());
					//arg0.setInt(index++, oos.getOper_type());
					arg0.setString(index++, oos.getCreate_oper_id());
					arg0.setString(index++, oos.getUpdate_oper_id());
				}
				
				@Override
				public int getBatchSize() {
					return omsOperStation.size();
				}
			});
		}
		catch(Exception e)
		{
			throw new OmsDaoException(e);
		}
	}
	/**
	 * 
	 * @param omsOperStations
	 * @throws OmsDaoException
	 */
	public void batchDeleteStationOperMerchant(final List<OmsOperStation> omsOperStations) throws OmsDaoException
	{
		String sql = DELETE_SQL;
		try
		{
			this.getJdbcTemplate().batchUpdate(sql, new BatchPreparedStatementSetter() {
				
				@Override
				public void setValues(PreparedStatement arg0, int arg1) throws SQLException 
				{
					int index = 1;
					OmsOperStation oos = omsOperStations.get(arg1);
					arg0.setString(index++, oos.getOper_id());
					arg0.setString(index++, oos.getStation_id());
					
				}
				
				@Override
				public int getBatchSize() {
					return omsOperStations.size();
				}
			});
		}
		catch(Exception e)
		{
			throw new OmsDaoException(e);
		}
	}



	public void physicalDeleteOperStations(List<OmsOperStation> operStations) throws OmsDaoException{
		if(operStations==null || operStations.size()==0){
			return;
		}
		StringBuilder sb = new StringBuilder("delete from oms_oper_station where ");
		for(OmsOperStation temp : operStations){
			sb.append("( oper_id ='"+temp.getOper_id()+"' and station_id = '"+temp.getStation_id() +"') or");
		}
		sb.deleteCharAt(sb.length()-1);
		sb.deleteCharAt(sb.length()-1);
		this.exec(sb.toString());
	}
	
}
